package weathercrowd.utilities;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import weathercrowd.model.*;


public class PersistenceHelper {
    private static final EntityManagerFactory emfInstance = Persistence
            .createEntityManagerFactory("puwc");
    
    private PersistenceHelper() {
    	
    }
 
    public static EntityManagerFactory get() {
        return emfInstance;
    }
    
    public static void beginTransaction(EntityTransaction transaction) {
    	if (!transaction.isActive()) {
    		transaction.begin();
    	}
    }
    
    /**
     * generates DDLs according to map definition by annotations and executes
     * the DDLs to create empty database tables overwriting existing ones if any
     * 
     * NOTE: so far with the current version of Hibernate I haven't found a way to obtain
     *       configuration from entity manager factory or create entity manager based on
     *       configuration, which means this create-table method has to be separately 
     *       maintained to be consistent with the settings in persistence.xml (mostly
     *       annotated class declarations) in order to generate DDLs that create the 
     *       right tables for the model   
     */
    public static void CreateTables() {
    	
    	Configuration cfg = new Configuration();

    	cfg.addAnnotatedClass(Temperature.class)
    		.addAnnotatedClass(BomLocation.class)
    		.addAnnotatedClass(Event.class)
    		.addAnnotatedClass(HistoricalRainfall.class)
    		.addAnnotatedClass(HistoricalTemperature.class)
    		.addAnnotatedClass(Location.class)
    		.addAnnotatedClass(Rainfall.class)
    		.addAnnotatedClass(User.class)
    		.addAnnotatedClass(UserLocation.class)
    		.addAnnotatedClass(UserRainfall.class)
    		.addAnnotatedClass(UserTemperature.class)
    		.setProperty(Environment.DRIVER, "org.postgresql.Driver")
    		.setProperty(Environment.DIALECT, "org.hibernate.dialect.HSQLDialect")
    		.setProperty(Environment.USER, "postgres")
    		.setProperty(Environment.PASS, "postgres")
    		.setProperty(Environment.URL, "jdbc:postgresql:weathercrowd")
    		.setProperty(Environment.MAX_FETCH_DEPTH, "3");
    	
    	SchemaExport schema = new SchemaExport(cfg);
   	    
    	// drops the tables first if any and creates all the tables needed
    	schema.execute(false, true, false, true);
    }
    
    public static void DropAllTables() {
		try {
	    	String username = "postgres";
			String password = "postgres";
			Connection conn = DriverManager.getConnection("jdbc:postgresql:weathercrowd", username, password);
	    	Statement sql = conn.createStatement();
	    	String[] sqlStrings = new String[] {
	    			"drop table if exists public.bomlocation cascade",
	    			"drop table if exists public.event cascade",
	    			"drop table if exists public.historicalrainfall cascade",
	    			"drop table if exists public.historicaltemperature cascade",
	    			"drop table if exists public.location cascade",
	    			"drop table if exists public.rainfall cascade",
	    			"drop table if exists public.temperature cascade",
	    			"drop table if exists public.user cascade",
	    			"drop table if exists public.userlocation cascade",
	    			"drop table if exists public.userrainfall cascade",
	    			"drop table if exists public.usertemperature cascade",
	    			// TODO add more tables here...
	    	};
	    	
	    	for (String sqlString : sqlStrings) {
		    	sql.executeUpdate(sqlString);
	    	}
	    	//String sqlText = "select 'drop table if exists ' || tablename || ' cascade;' from pg_tables";   	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
}
